﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Collections.Generic;
using Oracle.DataAccess.Types;
using Oracle.DataAccess.Client;
using System.Globalization;
using Ax.Frameworks.API;
using Ax.Frameworks.ValueObjects;
using Ax.Frameworks.SysUtils;

   class testCompile
    {
       /*
        *   Sample Code For Purpose of Illustration
        */
       class person 
       {
           public int id;
           public int age;
           public string name;
           public List <string> child;
       }

       static DataSet readExcelFile(string sFileName, string sSheetname)
       {
           OleDbConnection excelConnection = new OleDbConnection();
           excelConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ sFileName+";Extended Properties=Excel 12.0;Persist Security Info=False";
           excelConnection.Open();
           Console.WriteLine(excelConnection.State.ToString());
           string strCommand =  "select * from [" + sSheetname + "$]";
           OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter(strCommand, excelConnection);
           System.Data.DataSet dataSet = new System.Data.DataSet();
           excelDataAdapter.Fill(dataSet);
           excelConnection.Close();
           return dataSet;
       }

       static DataSet readEmployeeTable()
       {
           OracleConnection oc = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pion.modspace.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prod8)));User Id=MIG42;Password=MIG42$21");
           oc.Open();
           string strCommand = "select nvl(internal_id,0), nvl(f_name,l_name), l_name, nvl(last_update_date,sysdate) from employee";
           strCommand = "select data_length, table_name, column_name, sysdate from dba_tab_columns";
           OracleDataAdapter oda = new OracleDataAdapter(strCommand, oc);
           DataSet ds = new System.Data.DataSet();
           oda.Fill(ds);
           oc.Close();
           return ds;
       }

       static void BulkLoad()
       {
           Console.WriteLine(DateTime.Now.ToString());
           OracleConnection oc = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=pion.modspace.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prod8)));User Id=MIG42;Password=MIG42$21");
           oc.Open();
           OracleCommand cmd = oc.CreateCommand();
           string sql = "declare "
             + "type t_emp_id is table of bulk_test.employee_id%type index by pls_integer; "
             + "type t_first_name is table of bulk_test.first_name%type index by pls_integer; "
             + "type t_last_name is table of bulk_test.last_name%type index by pls_integer; "
             + "type t_dob is table of bulk_test.dob%type index by pls_integer; "
             + "p_emp_id t_emp_id; "
             + "p_first_name t_first_name; "
             + "p_last_name t_last_name; "
             + "p_dob t_dob; "
             + "begin "
             + "  p_emp_id := :emp_id; "
             + "  p_first_name := :first_name; "
             + "  p_last_name := :last_name; "
             + "  p_dob := :dob; "
             + "  forall i in p_emp_id.first..p_emp_id.last "
             + "    insert into bulk_test (employee_id, first_name, last_name, dob) "
             + "    values (p_emp_id(i), p_first_name(i) , p_last_name(i), p_dob(i)); "
             + "  open :c1 for "
             + "  select employee_id, first_name, last_name, dob from bulk_test;"
             + "end;";
           cmd.CommandText = sql;

           int numRecords = 0;

           List<int> employeeIds = new List<int>();
           List<string> firstNames = new List<string>();
           List<string> lastNames = new List<string>();
           List<DateTime> dobs = new List<DateTime>();

           DataSet ds = readEmployeeTable();
           foreach (DataRow row in ds.Tables[0].Rows)
           {
               employeeIds.Add(int.Parse(row[0].ToString()));
               firstNames.Add(row[1].ToString());
               lastNames.Add(row[2].ToString());
               dobs.Add(DateTime.Parse(row[3].ToString()));
               numRecords++;
           }

           OracleParameter pEmpId = new OracleParameter(":emp_id",
                                   OracleDbType.Int32,
                                   numRecords, ParameterDirection.Input);
           pEmpId.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
           pEmpId.Value = employeeIds.ToArray();

           OracleParameter pFirstName = new OracleParameter(":first_name",
                                   OracleDbType.Varchar2, numRecords,
                                   ParameterDirection.Input);
           pFirstName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
           pFirstName.Value = firstNames.ToArray();

           OracleParameter pLastName = new OracleParameter(":last_name",
                                   OracleDbType.Varchar2, numRecords,
                                   ParameterDirection.Input);
           pLastName.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
           pLastName.Value = lastNames.ToArray();

           OracleParameter pDob = new OracleParameter(":dob",
                                   OracleDbType.Date, numRecords,
                                   ParameterDirection.Input);
           pDob.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
           pDob.Value = dobs.ToArray();

           OracleParameter pRefCursor = new OracleParameter();
           pRefCursor.OracleDbType = OracleDbType.RefCursor;
           pRefCursor.Direction = ParameterDirection.ReturnValue;

           cmd.Parameters.Add(pEmpId);
           cmd.Parameters.Add(pFirstName);
           cmd.Parameters.Add(pLastName);
           cmd.Parameters.Add(pDob);
           cmd.Parameters.Add(pRefCursor);

           int rows = cmd.ExecuteNonQuery();

           //In case you want to check the output of the database.
           /*
           OracleDataReader dr = ((OracleRefCursor)pRefCursor.Value).GetDataReader();
           while (dr.Read())
           {
               // Process cursor
           }
           */
           oc.Close();
           Console.WriteLine(DateTime.Now.ToString());
       }

       static void Main(string[] args)
       {
           TestFactory();
           //BulkLoad();
           //writeFromFile();
       }

       static void TestFactory()
       {
           try
           {
               string conn;
               conn = "Data Source=s4twodev;User Id=s42_interface;Password=s42_interface$42;";
               Console.WriteLine(string.Format("{0}", conn));
               VOFactoryAPI factoryAPI = new VOFactoryAPI(conn, "Oracle");
               VOContract contract = (VOContract)factoryAPI.CreateVO(typeof(VOContract));
               contract.Initialize(true);
               contract.ToXML("newfile.xml");
               Console.WriteLine("Great Success");
           }
           catch (Exception x)
           {
               Console.WriteLine("Minor Failure");
               Console.WriteLine(x.ToString());
           }
       }
       static void writeFromFile()
       {
           DataSet ds = readExcelFile(@"f:\docs\Elto by Corp Entity_Sep-2010.xlsx", "Elto by Corp Entity");
           Console.WriteLine("Row count:{0}", ds.Tables[0].Rows.Count.ToString());

           //This loop will read column names (Row Header)
           for (int x = 0; x < ds.Tables[0].Columns.Count; x++)
           {
               string strOut = String.Format("Name:{0} Type:{1} ", ds.Tables[0].Columns[x].ColumnName, ds.Tables[0].Columns[x].DataType);
               Console.WriteLine(strOut);
           }

           Console.WriteLine("-".PadLeft(80, '-'));

           //This loop will read row contents
           foreach (DataRow row in ds.Tables[0].Rows)
           {
               for (int x = 0; x < ds.Tables[0].Columns.Count; x++)
               {
                   //Console.Write(row[x].ToString());
               }
               //Console.WriteLine("-".PadLeft(80, '-'));
           }
       }

        static void playwithPerson()
        {
            List<person> Listperson = new List<person>();

            for (int i = 1; i <= 2; i++)
            {
                person myperson = new person();
                //Assignment
                myperson.id = i;
                myperson.age = 22 + i;
                myperson.name = "Person - " + i.ToString();
                myperson.child = new List<string>();
                for (int c = 1; c <= 2; c++)
                {
                    myperson.child.Add("Child --" + c.ToString());
                }
                Listperson.Add(myperson);
            }
            int x = 0;
            while (x < Listperson.Count)
            {
                //Output values
                int c = 0;
                while (c < Listperson[x].child.Count)
                {
                    Console.WriteLine("{0} - {1} - {2}", Listperson[x].name, Listperson[x].age, Listperson[x].child[c].ToString());
                    c++;
                }
                Console.WriteLine("-----------------------------------------------------");
                x++;
            }
        }
    }